#include "oracledao.h"
#include"ComFun.h"

OracleDAO::OracleDAO(QString &szConnName)
{
    OracleDAO::m_staticMutex.lock();
    QString IniFilePath="config.ini";
    QSettings* m_DBSettings = new QSettings(IniFilePath,QSettings::IniFormat);
    QString DatabaseName = m_DBSettings->value("DB/DatabaseName","").toString();
    QString HostName = m_DBSettings->value("DB/HostName","").toString();
    int     Port = m_DBSettings->value("DB/Port","").toInt();
    QString UserName = m_DBSettings->value("DB/UserName","").toString();
    QString Password = m_DBSettings->value("DB/Password","").toString();
    delete m_DBSettings;

    m_OracleDB = QSqlDatabase::addDatabase("QOCI",szConnName);
    m_OracleDB.setDatabaseName(DatabaseName);
    m_OracleDB.setHostName(HostName);
    m_OracleDB.setPort(Port);
    m_OracleDB.setUserName(UserName);
    m_OracleDB.setPassword(Password);

    //char szErrMsg[szSTR] = "";
    if (m_OracleDB.open()){
        QString time = QTime::currentTime().toString("hh:mm:ss");
        qDebug() <<time <<" "<< szConnName.toStdString().c_str()<<": Connect To Oracle DataBase OK";
        //strncpy(szErrMsg,"Connect To Oracle DataBase OK.",szSTR);
        //logInfo.WriteLog(LOG_INFO,szErrMsg);
    }
    else{
        qDebug() << m_OracleDB.lastError().text();
        //strncpy(szErrMsg,m_OracleDB->lastError().text().toStdString().c_str(),szSTR);
        //logInfo.WriteLog(LOG_ERROR,szErrMsg);
    }
    OracleDAO::m_staticMutex.unlock();
}

OracleDAO::~OracleDAO() {

    QString name;
    {
        name = QSqlDatabase::database().connectionName();
        m_OracleDB.close();
    }//超出作用域，隐含对象QSqlDatabase::database()被删除。
    QSqlDatabase::removeDatabase(name);
}

//检查数据库连接是否可用，如果连接断开，则重新连接
bool OracleDAO::dbChecked(QSqlQuery &query)
{
    try {
        query.exec("select 1 from dual");
        if(query.next()) {
            return true;
        }
        if(m_OracleDB.open()) {
            return true;
        } else {
            return false;
        }
    } catch (QSqlError &e) {
        qDebug() << e.text();
        return false;
    }
}

//行情数据入库
void OracleDAO::UpdateHqInfo(QStringList &szHqList,QString &szDbName)
{
    //qDebug() << szHqList.size();
    QString quote_type           = szHqList.at(0 );
    QString update_time          = szHqList.at(2 );
    QString quote_index          = szHqList.at(3 );
    QString futu_exch_type       = szHqList.at(4 );
    QString commodity_type       = szHqList.at(5 );
    QString futu_code            = szHqList.at(6 );
    QString pre_settlement_price = szHqList.at(7 );
    QString pre_close_price      = szHqList.at(8 );
    QString pre_open_interest    = szHqList.at(9 );
    QString pre_delta            = szHqList.at(10);
    QString futu_open_price      = szHqList.at(11);
    QString futu_last_price      = szHqList.at(12);
    QString buy_high_price       = szHqList.at(13);
    QString buy_high_amount      = szHqList.at(14);
    QString low_sell_price       = szHqList.at(15);
    QString low_sell_amount      = szHqList.at(16);
    QString futu_high_price      = szHqList.at(17);
    QString futu_low_price       = szHqList.at(18);
    QString uplimited_price      = szHqList.at(19);
    QString downlimited_price    = szHqList.at(20);
    QString average_price        = szHqList.at(21);
    QString change_direction     = szHqList.at(22);
    QString business_amount      = szHqList.at(23);
    QString open_interest        = szHqList.at(24);
    QString business_balance     = szHqList.at(25);
    QString futu_close_price     = szHqList.at(26);
    QString square_price         = szHqList.at(27);
    QString curr_date            = QDateTime::currentDateTime().toString("yyyyMMdd");
    QString curr_delta           = "";

    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);
        QString szQrySql = "select curr_date from TB_TICK_DATA where "
            "curr_date='%1' and update_time='%2' and futu_code='%3'";
        szQrySql = szQrySql.arg(curr_date, update_time, futu_code);
        query.exec(szQrySql);
        QString szQryDate("");
        while (query.next()) {
            szQryDate = query.value("curr_date").toString();
        }
        query.clear();
        if(!szQryDate.trimmed().isEmpty() || update_time=="0") {
            return;
        }
        QString szUpdateSql("");
        szUpdateSql =
            "insert into TB_TICK_DATA(quote_type,update_time,quote_index,futu_exch_type,commodity_type,futu_code,"
            "pre_settlement_price,pre_close_price,pre_open_interest,pre_delta,futu_open_price,futu_last_price,buy_high_price,"
            "buy_high_amount,low_sell_price,low_sell_amount,futu_high_price,futu_low_price,uplimited_price,downlimited_price,"
            "average_price,change_direction,business_amount,open_interest,business_balance,futu_close_price,square_price,curr_date,curr_delta) "
            "values(:quoteType,:updateTime,:quoteIndex,:futuExchType,:commodityType,:futuCode,:preSetPrice,:preClosePrice,:preOpenInterest,"
            ":preDelta,:futuOpenPrice,:futuLastPrice,:buyHighPrice,:buyHighAmount,:lowSellPrice,:lowSellAmount,:futuHighPrice,:futuLowPrice,:uplimitedPrice,"
            ":downlimitedPrice,:averagePrice,:changeDirection,:businessAmount,:openInterest,:businessBalance,:futuClosePrice,:squarePrice,:currDate,:currDelta)";
        query.prepare(szUpdateSql);
        query.bindValue(":quoteType"       ,quote_type          );
        query.bindValue(":updateTime"      ,update_time         );
        query.bindValue(":quoteIndex"      ,quote_index         );
        query.bindValue(":futuExchType"    ,futu_exch_type      );
        query.bindValue(":commodityType"   ,commodity_type      );
        query.bindValue(":futuCode"        ,futu_code           );
        query.bindValue(":preSetPrice"     ,pre_settlement_price);
        query.bindValue(":preClosePrice"   ,pre_close_price     );
        query.bindValue(":preOpenInterest" ,pre_open_interest   );
        query.bindValue(":preDelta"        ,pre_delta           );
        query.bindValue(":futuOpenPrice"   ,futu_open_price     );
        query.bindValue(":futuLastPrice"   ,futu_last_price     );
        query.bindValue(":buyHighPrice"    ,buy_high_price      );
        query.bindValue(":buyHighAmount"   ,buy_high_amount     );
        query.bindValue(":lowSellPrice"    ,low_sell_price      );
        query.bindValue(":lowSellAmount"   ,low_sell_amount     );
        query.bindValue(":futuHighPrice"   ,futu_high_price     );
        query.bindValue(":futuLowPrice"    ,futu_low_price      );
        query.bindValue(":uplimitedPrice"  ,uplimited_price     );
        query.bindValue(":downlimitedPrice",downlimited_price   );
        query.bindValue(":averagePrice"    ,average_price       );
        query.bindValue(":changeDirection" ,change_direction    );
        query.bindValue(":businessAmount"  ,business_amount     );
        query.bindValue(":openInterest"    ,open_interest       );
        query.bindValue(":businessBalance" ,business_balance    );
        query.bindValue(":futuClosePrice"  ,futu_close_price    );
        query.bindValue(":squarePrice"     ,square_price        );
        query.bindValue(":currDate"        ,curr_date           );
        query.bindValue(":currDelta"       ,curr_delta          );
        query.exec();
        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
        }
        query.clear();
        //query.
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
bool OracleDAO::GetInstrumentCnt(QString &szDbName)
{
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select count(*) from TB_CONTRACT where FUTU_EXCH_TYPE!= 'F1' and FUTU_EXCH_TYPE!= 'F2' and FUTU_EXCH_TYPE!= 'F3' and FUTU_EXCH_TYPE!= 'F4'";

        query.prepare(szUpdateSql);
        query.exec();

        QString Cnt("");
        while (query.next()) {
            Cnt = query.value(0).toString();
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetInstrumentCnt OK:"<<Cnt;
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
void OracleDAO::UpdateInstruments(QStringList &szHqList,QString &szDbName)
{
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql =
            "insert into TB_CONTRACT(FUTU_EXCH_TYPE,CONTRACT_CODE,CONTRACT_TYPE,FUTU_PRICE_STEP,CONTRACT_NAME) "
            "values(:EXCHANGE_ID,:INSTRUMENT_ID,:PRODUCT_ID,:FUTU_PRICE_STEP,:CONTRACT_NAME)";
        query.prepare(szUpdateSql);
        query.bindValue(":EXCHANGE_ID"         ,szHqList.at(0)          );
        query.bindValue(":INSTRUMENT_ID"       ,szHqList.at(1)          );
        query.bindValue(":PRODUCT_ID"          ,szHqList.at(2)          );
        query.bindValue(":FUTU_PRICE_STEP"     ,szHqList.at(3)          );
        query.bindValue(":CONTRACT_NAME"     ,szHqList.at(4)          );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            QString stemp = "";
            for(int i = 0; i < szHqList.size(); i++){
                stemp.append(QString::number(i)).append(":").append(szHqList.at(i)).append(" ");
            }

            qDebug() << stemp;
            qDebug() << szErr;
        }else{
            //qDebug() << "UpdateInstrument OK:"<<szHqList.at(0)<<"-"<<szHqList.at(1);
        }
        query.clear();

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
bool OracleDAO::DeleteOldInstruments(QString &szDbName)
{
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "delete from TB_CONTRACT where FUTU_EXCH_TYPE!= 'F1' and FUTU_EXCH_TYPE!= 'F2' and FUTU_EXCH_TYPE!= 'F3' and FUTU_EXCH_TYPE!= 'F4'";

        query.prepare(szUpdateSql);
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "DeleteOldInstrument OK";
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

//行情数据入库
void OracleDAO::UpdateCTPMarketInfo(QStringList &szHqList,QString &szDbName)
{
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);
        QString szUpdateSql("");
        szUpdateSql = "insert into TB_CTP_TICKDATA values(:TRADEDATE,:INSTRUMENT_ID,:EXCHANGE_ID,:EXCHANGE_INST_ID,:LAST_PRICE,:PRE_SETTLEMENT_PRICE,:PRE_CLOSE_PRICE,:PRE_OPEN_INTEREST,:OPEN_PRICE,"
			":HIGHEST_PRICE,:LOWEST_PRICE,:VOLUME,:TURNOVER,:OPEN_INTEREST,:CLOSE_PRICE,:SETTLEMENT_PRICE,:UPPER_LIMIT_PRICE,:LOWER_LIMIT_PRICE,:PRE_DELTA,"
			":CURR_DELTA,:UPDATE_TIME,:UPDATE_MILLISEC,:BID_PRICE1,:BID_VOLUME1,:ASK_PRICE1,:ASK_VOLUME1,:BID_PRICE2,:BID_VOLUME2,:ASK_PRICE2,:ASK_VOLUME2,:BID_PRICE3,:BID_VOLUME3,:ASK_PRICE3"
			",:ASK_VOLUME3,:BID_PRICE4,:BID_VOLUME4,:ASK_PRICE4,:ASK_VOLUME4,:BID_PRICE5,:BID_VOLUME5,:ASK_PRICE5,:ASK_VOLUME5,:AVERAGE_PRICE,:ACTION_DAY,:DAYFLAG)";

        query.prepare(szUpdateSql);
        query.bindValue(":TRADEDATE"         ,szHqList.at(0)          );
        query.bindValue(":INSTRUMENT_ID"       ,szHqList.at(1)          );
        query.bindValue(":EXCHANGE_ID"         ,szHqList.at(2)          );
        query.bindValue(":EXCHANGE_INST_ID"    ,szHqList.at(3)          );
        if(szHqList.at(4).toDouble() == 1.79769e+308 ){
            query.bindValue(":LAST_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":LAST_PRICE"    ,szHqList.at(4)     );
        }
        if(szHqList.at(5).toDouble() == 1.79769e+308 ){
            query.bindValue(":PRE_SETTLEMENT_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":PRE_SETTLEMENT_PRICE"    ,szHqList.at(5)     );
        }
        if(szHqList.at(6).toDouble() == 1.79769e+308 ){
            query.bindValue(":PRE_CLOSE_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":PRE_CLOSE_PRICE"    ,szHqList.at(6)     );
        }
        if(szHqList.at(7).toDouble() == 1.79769e+308 ){
            query.bindValue(":PRE_OPEN_INTEREST"    ,QString::number(0)  );
        }else{
            query.bindValue(":PRE_OPEN_INTEREST"    ,szHqList.at(7)     );
        }
        if(szHqList.at(8).toDouble() == 1.79769e+308 ){
            query.bindValue(":OPEN_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":OPEN_PRICE"    ,szHqList.at(8)     );
        }
        if(szHqList.at(9).toDouble() == 1.79769e+308 ){
            query.bindValue(":HIGHEST_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":HIGHEST_PRICE"    ,szHqList.at(9)     );
        }
        if(szHqList.at(10).toDouble() == 1.79769e+308 ){
            query.bindValue(":LOWEST_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":LOWEST_PRICE"    ,szHqList.at(10)     );
        }
        query.bindValue(":VOLUME"              ,szHqList.at(11)         );
        if(szHqList.at(12).toDouble() == 1.79769e+308 ){
            query.bindValue(":TURNOVER"    ,QString::number(0)  );
        }else{
            query.bindValue(":TURNOVER"    ,szHqList.at(12)     );
        }
        if(szHqList.at(13).toDouble() == 1.79769e+308 ){
            query.bindValue(":OPEN_INTEREST"    ,QString::number(0)  );
        }else{
            query.bindValue(":OPEN_INTEREST"    ,szHqList.at(13)     );
        }
        if(szHqList.at(14).toDouble() == 1.79769e+308 ){
            query.bindValue(":CLOSE_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":CLOSE_PRICE"    ,szHqList.at(14)     );
        }
        if(szHqList.at(15).toDouble() == 1.79769e+308 ){
            query.bindValue(":SETTLEMENT_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":SETTLEMENT_PRICE"    ,szHqList.at(15)     );
        }
        if(szHqList.at(16).toDouble() == 1.79769e+308 ){
            query.bindValue(":UPPER_LIMIT_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":UPPER_LIMIT_PRICE"    ,szHqList.at(16)     );
        }
        if(szHqList.at(17).toDouble() == 1.79769e+308 ){
            query.bindValue(":LOWER_LIMIT_PRICE"    ,QString::number(0)  );
        }else{
            query.bindValue(":LOWER_LIMIT_PRICE"    ,szHqList.at(17)     );
        }
        if(szHqList.at(18).toDouble() == 1.79769e+308 ){
            query.bindValue(":PRE_DELTA"    ,QString::number(0)  );
        }else{
            query.bindValue(":PRE_DELTA"    ,szHqList.at(18)     );
        }
        if(szHqList.at(19).toDouble() == 1.79769e+308 ){
            query.bindValue(":CURR_DELTA"    ,QString::number(0)  );
        }else{
            query.bindValue(":CURR_DELTA"    ,szHqList.at(19)     );
        }

        QString strUpdateTime = szHqList.at(20).trimmed();
        strUpdateTime = strUpdateTime.remove(2,1);
        strUpdateTime = strUpdateTime.remove(4,1);
        QString strUpdateMillisec = szHqList.at(21).trimmed();
        if(strUpdateMillisec.size() == 1){
            strUpdateTime = strUpdateTime + "00" + strUpdateMillisec;
        }else if(strUpdateMillisec.size() == 2){
            strUpdateTime = strUpdateTime + "0" + strUpdateMillisec;
        }else if(strUpdateMillisec.size() == 3){
            strUpdateTime = strUpdateTime + strUpdateMillisec;
        }
        query.bindValue(":UPDATE_TIME"         ,strUpdateTime       );
        query.bindValue(":UPDATE_MILLISEC"     ,szHqList.at(21)         );

        if(szHqList.at(22).toDouble() == 1.79769e+308 ){
            query.bindValue(":BID_PRICE1"    ,QString::number(0)  );
        }else{
            query.bindValue(":BID_PRICE1"    ,szHqList.at(22)     );
        }
        query.bindValue(":BID_VOLUME1"         ,szHqList.at(23)         );
        if(szHqList.at(24).toDouble() == 1.79769e+308 ){
            query.bindValue(":ASK_PRICE1"    ,QString::number(0)  );
        }else{
            query.bindValue(":ASK_PRICE1"    ,szHqList.at(24)     );
        }
        query.bindValue(":ASK_VOLUME1"         ,szHqList.at(25)         );
        if(szHqList.at(26).toDouble() == 1.79769e+308 ){
            query.bindValue(":BID_PRICE2"    ,QString::number(0)  );
        }else{
            query.bindValue(":BID_PRICE2"    ,szHqList.at(26)     );
        }
        query.bindValue(":BID_VOLUME2"         ,szHqList.at(27)         );
        if(szHqList.at(28).toDouble() == 1.79769e+308 ){
            query.bindValue(":ASK_PRICE2"    ,QString::number(0)  );
        }else{
            query.bindValue(":ASK_PRICE2"    ,szHqList.at(28)     );
        }
        query.bindValue(":ASK_VOLUME2"         ,szHqList.at(29)         );
        if(szHqList.at(30).toDouble() == 1.79769e+308 ){
            query.bindValue(":BID_PRICE3"    ,QString::number(0)  );
        }else{
            query.bindValue(":BID_PRICE3"    ,szHqList.at(30)     );
        }
        query.bindValue(":BID_VOLUME3"         ,szHqList.at(31)         );
        if(szHqList.at(32).toDouble() == 1.79769e+308 ){
            query.bindValue(":ASK_PRICE3"    ,QString::number(0)  );
        }else{
            query.bindValue(":ASK_PRICE3"    ,szHqList.at(32)     );
        }
        query.bindValue(":ASK_VOLUME3"         ,szHqList.at(33)         );
        if(szHqList.at(34).toDouble() == 1.79769e+308 ){
            query.bindValue(":BID_PRICE4"    ,QString::number(0)  );
        }else{
            query.bindValue(":BID_PRICE4"    ,szHqList.at(34)     );
        }
        query.bindValue(":BID_VOLUME4"         ,szHqList.at(35)         );
        if(szHqList.at(36).toDouble() == 1.79769e+308 ){
            query.bindValue(":ASK_PRICE4"    ,QString::number(0)  );
        }else{
            query.bindValue(":ASK_PRICE4"    ,szHqList.at(36)     );
        }
        query.bindValue(":ASK_VOLUME4"         ,szHqList.at(37)         );
        if(szHqList.at(38).toDouble() == 1.79769e+308 ){
            query.bindValue(":BID_PRICE5"    ,QString::number(0)  );
        }else{
            query.bindValue(":BID_PRICE5"    ,szHqList.at(38)     );
        }
        query.bindValue(":BID_VOLUME5"         ,szHqList.at(39)         );
        if(szHqList.at(40).toDouble() == 1.79769e+308 ){
            query.bindValue(":ASK_PRICE5"    ,QString::number(0)  );
        }else{
            query.bindValue(":ASK_PRICE5"    ,szHqList.at(40)     );
        }
        query.bindValue(":ASK_VOLUME5"         ,szHqList.at(41)         );
        query.bindValue(":AVERAGE_PRICE"       ,szHqList.at(42)         );
        query.bindValue(":ACTION_DAY"          ,szHqList.at(43)         );

        QString strDAYFLAG = szHqList.at(0).right(2);
        query.bindValue(":DAYFLAG"          ,strDAYFLAG         );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            QString stemp = "";
            for(int i = 0; i < szHqList.size(); i++){
                stemp.append(QString::number(i)).append(":").append(szHqList.at(i)).append(" ");
            }

            qDebug() << stemp;
            qDebug() << szErr;
        }else{
            qDebug() << "Insert OK:"<<szHqList.at(1)<<" "<<strUpdateTime;
        }
        query.clear();

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
bool OracleDAO::GetInstruments(QString &szDbName,std::vector<QString>& ContractCodes,QMap<QString,ContractType> &MapContractType)
{
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select  t.contract_code,t.futu_exch_type,replace (replace(t.contract_type,'&','_'),' ','') from TB_CONTRACT t where length(t.futu_exch_type) > 2";
        query.prepare(szUpdateSql);
        query.exec();

        while (query.next()) {
            ContractCodes.push_back(query.value(0).toString());

            ContractType oContract;
            oContract.contractCode = query.value(0).toString();
            oContract.futuExchType = query.value(1).toString();
            oContract.contractType = query.value(2).toString();
            MapContractType.insert(query.value(0).toString(),oContract);
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetInstruments OK:"<<ContractCodes.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::MatchThread(QString &szDbName,QMap<QString,int> &MapMatchThrd){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select t.futu_code from TB_KLINE_1DAY_CTP t "
                "where t.curr_date in (select * from (select distinct t1.tradedate from tb_ctp_zhulian t1 order by t1.tradedate desc ) where rownum <= 1) "
                "and   t.futu_code  in (select t2.contract_code from TB_CONTRACT t2 where length(t2.futu_exch_type) > 2  )  "
                "order by t.business_ammount desc";
        query.prepare(szUpdateSql);
        query.exec();

        int iIndex = 0;
        while (query.next()) {
            MapMatchThrd.insert(query.value(0).toString(),iIndex % WORK_QUEUE);
            iIndex++;
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "MatchThread OK:"<<MapMatchThrd.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::GetZhulian(QString &szDbName,QMap<QString,QString> &mapZhulian){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select * from  TB_CTP_ZHULIAN t1 where t1.tradedate "
                " in( select * from (select distinct t.tradedate from TB_CTP_ZHULIAN t  order by t.tradedate desc) where rownum =1)";
        query.prepare(szUpdateSql);
        query.exec();

        while (query.next()) {
            mapZhulian.insert(query.value(2).toString(),query.value(0).toString());
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetZhulian OK:"<<mapZhulian.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
//获取所有合约的前一交易日最后一根5分钟K线的信息
bool    OracleDAO::GetAllPre5MinKLine(QString &szDbName,QString strPreTradeDay){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select t.futu_code,t.business_ammount,t.position_ammount  from TB_KLINE_5MIN_CTP t "
                "where t.curr_date = :TRADEDATE and (t.curr_time like '15:15:%' or t.curr_time like '15:00:%' ) "
                "and t.futu_code not like '%Jinyue' order by t.curr_time desc ";
        query.prepare(szUpdateSql);
        query.bindValue(":TRADEDATE",strPreTradeDay);
        query.exec();

        while (query.next()) {
            KLineType kLine;
            if(query.value(1).isValid()){
                kLine.PreBusinessAmount = query.value(1).toInt();
            }
            if(query.value(2).isValid()){
                kLine.PreOpenInterest   = query.value(2).toInt();
            }
            m_Pre5MinKLine.insert(query.value(0).toString(),kLine);
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetAllPre5MinKLine OK:"<<m_MapPreVolume.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
//获取所有合约的前一交易日的成交量
bool    OracleDAO::GetAllPreVolume(QString &szDbName,QString strPreTradeDay){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select t.futu_code,t.business_ammount from TB_KLINE_1DAY_CTP t where t.futu_code not like '%Jinyue' "
                " and t.curr_date = :TRADEDATE  order by t.curr_date desc ";
        query.prepare(szUpdateSql);
        query.bindValue(":TRADEDATE",strPreTradeDay);
        query.exec();

        while (query.next()) {
            m_MapPreVolume.insert(query.value(0).toString(),query.value(1).toInt());
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetAllPreVolume OK:"<<m_MapPreVolume.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
//获取前一个交易日日期
QString OracleDAO::GetPreTradeDay(QString &szDbName){
    try {
        QString   strPreTradeDay;
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select * from ( "
                      "select distinct t.curr_date from TB_KLINE_1DAY_CTP t order by t.curr_date desc) "
                       "where rownum <= 1";
        query.prepare(szUpdateSql);
        query.exec();

        while (query.next()) {
            strPreTradeDay = query.value(0).toString();
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return "";
        }else{
            qDebug() << "GetPreTradeDay OK:"<<strPreTradeDay;
        }
        query.clear();
        return strPreTradeDay;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::GetJinyue(QString &szDbName,QMap<QString,QString> &mapJinyue){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select * from  TB_CTP_JINYUE t1 where t1.tradedate "
                " in( select * from (select distinct t.tradedate from TB_CTP_JINYUE t  order by t.tradedate desc) where rownum =1)";
        query.prepare(szUpdateSql);
        query.exec();

        while (query.next()) {
            mapJinyue.insert(query.value(2).toString(),query.value(0).toString());
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetJinyue OK:"<<mapJinyue.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::GetContractTypes(QString &szDbName,std::vector<QString>& ContractTypes){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select distinct t.contract_type from tb_contract t  where length(t.futu_exch_type)>2";
        query.prepare(szUpdateSql);
        query.exec();

        while (query.next()) {
            ContractTypes.push_back(query.value(0).toString());
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            qDebug() << "GetContractTypes OK:"<<ContractTypes.size();
        }
        query.clear();
        return true;

    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::GetTheMainContractCode(QString &szDbName,QString ContractType,QString date,QString &MainContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        szUpdateSql = "select * from ( select t1.futu_code from tb_kline_1day_ctp t1 where t1.curr_date = :TRADEDATE and t1.futu_code "
                "in (select t.contract_code from tb_contract t where length(t.futu_exch_type)>2 and t.contract_type = :CONTRACTTYPE) order by t1.business_ammount desc,t1.futu_code )where rownum <=1";
        query.prepare(szUpdateSql);
        query.bindValue(":TRADEDATE",date);
        query.bindValue(":CONTRACTTYPE",ContractType);
        query.exec();

        bool bFlg = false;
        while (query.next()) {
            MainContractCode = query.value(0).toString();
            bFlg = true;
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() <<ContractType<<" "<< szErr;
            query.clear();
            return false;
        }
        query.clear();
        return bFlg;
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
//获取各品种对应的近月合约代码
bool OracleDAO::GetTheJinyueContractCode(QString &szDbName,QString ContractType,QString &JinyueContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString szUpdateSql("");
        //szUpdateSql = "select * from (select t.contract_code from tb_contract t where length(t.futu_exch_type)>2 and t.contract_type = :CONTRACTTYPE order by t.contract_code )where rownum <= 1";
        //更改SQL语句，根据日K线表中记录，来筛选有效的近月合约代码
        szUpdateSql = "select * from(select t.futu_code from  Tb_Kline_1day_Ctp t "
        "where t.futu_code in (select t1.contract_code from tb_contract t1 where length(t1.futu_exch_type)>2 and t1.contract_type = :CONTRACTTYPE) "
        "order by t.curr_date desc,t.futu_code) where rownum <= 1";

        query.prepare(szUpdateSql);
        query.bindValue(":CONTRACTTYPE",ContractType);
        query.exec();

        bool bFlg = false;
        while (query.next()) {
            JinyueContractCode = query.value(0).toString();
            bFlg = true;
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() <<ContractType<<" "<< szErr;
            query.clear();
            return false;
        }
        query.clear();
        return bFlg;
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::InsertZhulianRec(QString &szDbName,QString zhulianCode,QString date,QString ContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString querySql("");
        querySql = "insert into tb_ctp_zhulian(zhulian_code,tradedate,contract_code) "
                    "values(:zhulian_code,:tradedate,:contract_code)";
        query.prepare(querySql);
        query.bindValue(":zhulian_code"  ,zhulianCode      );
        query.bindValue(":tradedate"     ,date     );
        query.bindValue(":contract_code" ,ContractCode     );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr ;
            qDebug() << zhulianCode << " "<<date<<" "<< ContractCode;
        }else{
            qDebug() << "ZhulianTable Insert OK:"<<zhulianCode<<" "<<ContractCode;
        }
        query.clear();
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
    return true;
}

bool OracleDAO::SaveZhulianDayKLineData(QString &szDbName,QString zhulianCode,QString date,QString ContractCode,QMap<QString,ContractType> &MapContractType){
    try {
        //查询主连合约是否有对应的日K线数据
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);
        QString querySql("");
        querySql = "select * from tb_kline_1day_ctp t where t.futu_code = :ContractName and t.curr_date = :TRADEDATE";
        query.prepare(querySql);
        query.bindValue(":ContractName"  ,ContractCode      );
        query.bindValue(":TRADEDATE"     ,date     );
        query.exec();

        std::vector<QString> QueryValues;
        while (query.next()){ //Only One Row Data
            QueryValues.push_back(query.value("CURR_DATE").toString());//0
            QueryValues.push_back(query.value("CURR_TIME").toString());//1
            QueryValues.push_back(query.value("MAX_PRICE").toString());//2
            QueryValues.push_back(query.value("MIN_PRICE").toString());//3
            QueryValues.push_back(query.value("OPEN_PRICE").toString());//4
            QueryValues.push_back(query.value("CLOSE_PRICE").toString());//5
            QueryValues.push_back(query.value("SETTLE_PRICE").toString());//6
            QueryValues.push_back(query.value("PRE_SETTLEMENT").toString());//7
            QueryValues.push_back(query.value("PRE_CLOSE_PRICE").toString());//8
            QueryValues.push_back(query.value("BUSINESS_AMMOUNT").toString());//9
            ///add start 2016.8.1
            QueryValues.push_back(query.value("BUSINESS_CHANGE").toString()); //10 成交 变化量
            QueryValues.push_back(query.value("POSITION_AMMOUNT").toString());//11 持仓量
            QueryValues.push_back(query.value("POSITION_CHANGE").toString()); //12 持仓 变化量
            ///add end   2016.8.1
            break;
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            if(QueryValues.size() != 13){//主连合约没有对应的合约数据
                qDebug() << "No Relevant DayKLineData:"<<zhulianCode<< " "<<ContractCode;
            }else{//主连合约有对应的合约数据
                QString tableName = "";
                if(MapContractType.contains(ContractCode)){
                    ContractType oContract = MapContractType[ContractCode];
                    tableName = "TB_KLINE_ZL_1DAY_CTP_"+oContract.contractType;
                }else{
                    tableName = "TB_KLINE_1DAY_CTP";
                }
                //首先查询数据库中是否已经存在对应的K线记录
                QSqlQuery Match(QSqlDatabase::database(szDbName));
                QString strMatchSql = "select count(*) from " + tableName + " t where t.pre_close_price is null ";
                int     iCnt = 0;
                Match.prepare(strMatchSql);
                Match.exec();
                while (Match.next()) {
                    iCnt = Match.value(0).toInt();
                }
                QString szErr = Match.lastError().text();
                if(!szErr.trimmed().isEmpty()) {
                    qDebug() << szErr;
                    Match.clear();
                }else{
                    if( iCnt == 0 ){ //数据库中没有对应的K线记录 进行Insert操作
                        QSqlQuery Insert(QSqlDatabase::database(szDbName));
                        dbChecked(Insert);

                        QString InsertSql = "insert into " + tableName +
                                "(FUTU_CODE,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,OPEN_PRICE,CLOSE_PRICE,SETTLE_PRICE,"
                                "PRE_SETTLEMENT,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT,BUSINESS_CHANGE,"
                                "POSITION_AMMOUNT,POSITION_CHANGE) "
                                "values(:FUTU_CODE,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,:CLOSE_PRICE,"
                                ":SETTLE_PRICE,:PRE_SETTLEMENT,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT,"
                                ":BUSINESS_CHANGE,:POSITION_AMMOUNT,:POSITION_CHANGE)";
                        Insert.prepare(InsertSql);
                        Insert.bindValue(":FUTU_CODE"       ,ContractCode      );
                        Insert.bindValue(":CURR_DATE"       ,QueryValues[0]     );
                        Insert.bindValue(":CURR_TIME"       ,QueryValues[1]     );
                        Insert.bindValue(":MAX_PRICE"       ,QueryValues[2]     );
                        Insert.bindValue(":MIN_PRICE"       ,QueryValues[3]     );
                        Insert.bindValue(":OPEN_PRICE"      ,QueryValues[4]     );
                        Insert.bindValue(":CLOSE_PRICE"     ,QueryValues[5]     );
                        Insert.bindValue(":SETTLE_PRICE"    ,QueryValues[6]     );
                        Insert.bindValue(":PRE_SETTLEMENT"  ,QueryValues[7]     );
                        Insert.bindValue(":PRE_CLOSE_PRICE" ,QueryValues[8]     );
                        Insert.bindValue(":BUSINESS_AMMOUNT" ,QueryValues[9]     );
                        ///add start 2016.8.1
                        Insert.bindValue(":BUSINESS_CHANGE" ,QueryValues[10]     );//10 成交 变化量
                        Insert.bindValue(":POSITION_AMMOUNT" ,QueryValues[11]    );//11 持仓量
                        Insert.bindValue(":POSITION_CHANGE" ,QueryValues[12]     );//12 持仓 变化量
                        ///add end 2016.8.1
                        Insert.exec();

                        QString szInsertErr = Insert.lastError().text();
                        if(!szInsertErr.trimmed().isEmpty()) {
                            qDebug() << szErr;
                            query.clear();
                            Insert.clear();
                            return false;
                        }else{
                            qDebug() << "DayKLineTable Insert OK:"<<zhulianCode<< " "<<ContractCode;;
                            query.clear();
                            Insert.clear();
                            return true;
                        }
                    }else if(iCnt == 1){//数据库中已存在对应的K线记录 进行Update操作
                        QSqlQuery Update(QSqlDatabase::database(szDbName));
                        QString   strUpdateSql = "update " + tableName + " t set t.CURR_TIME = :CURR_TIME,"
                                "t.MAX_PRICE = :MAX_PRICE,t.MIN_PRICE=:MIN_PRICE,t.OPEN_PRICE =:OPEN_PRICE,t.CLOSE_PRICE=:CLOSE_PRICE ,"
                                "t.SETTLE_PRICE = :SETTLE_PRICE,t.PRE_SETTLEMENT = :PRE_SETTLEMENT,t.PRE_CLOSE_PRICE = :PRE_CLOSE_PRICE,"
                                "t.BUSINESS_AMMOUNT = :BUSINESS_AMMOUNT,t.BUSINESS_CHANGE = :BUSINESS_CHANGE,"
                                "t.POSITION_AMMOUNT = :POSITION_AMMOUNT,t.POSITION_CHANGE = :POSITION_CHANGE "
                                "where t.pre_close_price is null";
                        Update.prepare(strUpdateSql);
                        Update.bindValue(":CURR_TIME"       ,QueryValues[1]     );
                        Update.bindValue(":MAX_PRICE"       ,QueryValues[2]     );
                        Update.bindValue(":MIN_PRICE"       ,QueryValues[3]     );
                        Update.bindValue(":OPEN_PRICE"      ,QueryValues[4]     );
                        Update.bindValue(":CLOSE_PRICE"     ,QueryValues[5]     );
                        Update.bindValue(":SETTLE_PRICE"    ,QueryValues[6]     );
                        Update.bindValue(":PRE_SETTLEMENT"  ,QueryValues[7]     );
                        Update.bindValue(":PRE_CLOSE_PRICE" ,QueryValues[8]     );
                        Update.bindValue(":BUSINESS_AMMOUNT" ,QueryValues[9]    );
                        ///add start 2016.8.1
                        Update.bindValue(":BUSINESS_CHANGE" ,QueryValues[10]     );//10 成交 变化量
                        Update.bindValue(":POSITION_AMMOUNT" ,QueryValues[11]    );//11 持仓量
                        Update.bindValue(":POSITION_CHANGE" ,QueryValues[12]     );//12 持仓 变化量
                        ///add end 2016.8.1
                        Update.exec();

                        QString szUpdateErr = Update.lastError().text();
                        if(!szUpdateErr.trimmed().isEmpty()) {
                            qDebug() << szUpdateErr;
                            query.clear();
                            Update.clear();
                        }else{
                            qDebug() << "DayKLineTable Update OK:"<<ContractCode;;
                            query.clear();
                            Update.clear();
                        }
                    }else{ //数据库中查询出多条对应的K线记录 出现这种情况是错误的
                        qDebug() << "DayKLineTable Merge Error:"<<ContractCode;
                    }
                }
            }
        }
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

//制作主连Tick数据
bool OracleDAO::SaveZhulianTickData(QString &szDbName,QString zhulianCode,QString date,QString ContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString querySql("");
        querySql = "insert into Tb_Ctp_Tickdata_Zl(TRADEDATE,Instrument_Id,Exchange_Id,Exchange_Inst_Id,"
                "Last_Price,Pre_Settlement_Price,Pre_Close_Price,Pre_Open_Interest,Open_Price,Highest_Price,"
                "Lowest_Price,Volume,Turnover,Open_Interest,Close_Price,Settlement_Price,Upper_Limit_Price,"
                "Lower_Limit_Price,Pre_Delta,Curr_Delta,Update_Time,Update_Millisec,"
                "Bid_Price1,Bid_Volume1,Ask_Price1,Ask_Volume1,"
                "Bid_Price2,Bid_Volume2,Ask_Price2,Ask_Volume2,"
                "Bid_Price3,Bid_Volume3,Ask_Price3,Ask_Volume3,"
                "Bid_Price4,Bid_Volume4,Ask_Price4,Ask_Volume4,"
                "Bid_Price5,Bid_Volume5,Ask_Price5,Ask_Volume5,"
                "Average_Price,Action_Day,Dayflag) (select * from tb_ctp_tickdata t where t.tradedate = :TRADEDATE and t.instrument_id = :ContractName )";
        query.prepare(querySql);
        query.bindValue(":ContractName"  ,ContractCode      );
        query.bindValue(":TRADEDATE"     ,date     );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            QSqlQuery update(QSqlDatabase::database(szDbName));
            dbChecked(update);
            QString UpdateSql = "update tb_ctp_tickdata_zl t set t.ZHULIAN_CODE = :ZHULIAN_CODE where t.tradedate = :CURR_DATE and t.instrument_id = :FUFU_CODE";
            update.prepare(UpdateSql);
            update.bindValue(":ZHULIAN_CODE"    ,zhulianCode);
            update.bindValue(":CURR_DATE"       ,date     );
            update.bindValue(":FUFU_CODE"       ,ContractCode);
            update.exec();

            QString szInsertErr = update.lastError().text();
            if(!szInsertErr.trimmed().isEmpty()) {
                qDebug() << szErr;
                query.clear();
                update.clear();
                return false;
            }else{
                qDebug() << "ZhulianTickData Insert OK:"<<zhulianCode<< " "<<ContractCode;;
                query.clear();
                update.clear();
                return true;
            }
        }
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::InsertJinyueRec(QString &szDbName,QString jinyueCode,QString date,QString ContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString querySql("");
        querySql = "insert into tb_ctp_jinyue(jinyue_code,tradedate,contract_code) "
                    "values(:zhulian_code,:tradedate,:contract_code)";
        query.prepare(querySql);
        query.bindValue(":zhulian_code"  ,jinyueCode      );
        query.bindValue(":tradedate"     ,date     );
        query.bindValue(":contract_code" ,ContractCode     );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
        }else{
            qDebug() << "Insert OK:"<<jinyueCode;
        }
        query.clear();
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
    return true;
}

bool OracleDAO::SaveJinyueDayKLineData(QString &szDbName,QString jinyueCode,QString date,QString ContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString querySql("");
        querySql = "select * from tb_kline_1day_ctp t where t.futu_code = :ContractName and t.curr_date = :TRADEDATE";
        query.prepare(querySql);
        query.bindValue(":ContractName"  ,ContractCode      );
        query.bindValue(":TRADEDATE"     ,date     );
        query.exec();

        std::vector<QString> QueryValues;
        while (query.next()) { //Only One Row Data
            QueryValues.push_back(query.value("CURR_DATE").toString());
            QueryValues.push_back(query.value("CURR_TIME").toString());
            QueryValues.push_back(query.value("MAX_PRICE").toString());
            QueryValues.push_back(query.value("MIN_PRICE").toString());
            QueryValues.push_back(query.value("OPEN_PRICE").toString());
            QueryValues.push_back(query.value("CLOSE_PRICE").toString());
            QueryValues.push_back(query.value("SETTLE_PRICE").toString());
            QueryValues.push_back(query.value("PRE_SETTLEMENT").toString());
            QueryValues.push_back(query.value("PRE_CLOSE_PRICE").toString());
            QueryValues.push_back(query.value("BUSINESS_AMMOUNT").toString());
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            if(QueryValues.size() != 10){
                qDebug() << "No Relevant DayKLineData:"<<jinyueCode<< " "<<ContractCode;;
            }else{
                QSqlQuery Insert(QSqlDatabase::database(szDbName));
                dbChecked(Insert);
                QString InsertSql = "insert into TB_KLINE_1DAY_CTP(FUTU_CODE,CURR_DATE,CURR_TIME,MAX_PRICE,"
                        "MIN_PRICE,OPEN_PRICE,CLOSE_PRICE,SETTLE_PRICE,PRE_SETTLEMENT,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT) "
                        "values(:FUTU_CODE,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,"
                        ":CLOSE_PRICE,:SETTLE_PRICE,:PRE_SETTLEMENT,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT)";
                Insert.prepare(InsertSql);
                Insert.bindValue(":FUTU_CODE"       ,jinyueCode      );
                Insert.bindValue(":CURR_DATE"       ,QueryValues[0]     );
                Insert.bindValue(":CURR_TIME"       ,QueryValues[1]     );
                Insert.bindValue(":MAX_PRICE"       ,QueryValues[2]     );
                Insert.bindValue(":MIN_PRICE"       ,QueryValues[3]     );
                Insert.bindValue(":OPEN_PRICE"      ,QueryValues[4]     );
                Insert.bindValue(":CLOSE_PRICE"     ,QueryValues[5]     );
                Insert.bindValue(":SETTLE_PRICE"    ,QueryValues[6]     );
                Insert.bindValue(":PRE_SETTLEMENT"  ,QueryValues[7]     );
                Insert.bindValue(":PRE_CLOSE_PRICE" ,QueryValues[8]     );
                Insert.bindValue(":BUSINESS_AMMOUNT" ,QueryValues[9]     );
                Insert.exec();

                QString szInsertErr = Insert.lastError().text();
                if(!szInsertErr.trimmed().isEmpty()) {
                    qDebug() << szErr;
                    query.clear();
                    Insert.clear();
                    return false;
                }else{
                    qDebug() << "DayKLineTable Insert OK:"<<jinyueCode<< " "<<ContractCode;;
                    query.clear();
                    Insert.clear();
                    return true;
                }
            }
        }
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::SaveJinyueTickData(QString &szDbName,QString jinyueCode,QString date,QString ContractCode){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        QString querySql("");
        querySql = "insert into Tb_Ctp_Tickdata_jy(TRADEDATE,Instrument_Id,Exchange_Id,Exchange_Inst_Id,"
                "Last_Price,Pre_Settlement_Price,Pre_Close_Price,Pre_Open_Interest,Open_Price,Highest_Price,"
                "Lowest_Price,Volume,Turnover,Open_Interest,Close_Price,Settlement_Price,Upper_Limit_Price,"
                "Lower_Limit_Price,Pre_Delta,Curr_Delta,Update_Time,Update_Millisec,"
                "Bid_Price1,Bid_Volume1,Ask_Price1,Ask_Volume1,"
                "Bid_Price2,Bid_Volume2,Ask_Price2,Ask_Volume2,"
                "Bid_Price3,Bid_Volume3,Ask_Price3,Ask_Volume3,"
                "Bid_Price4,Bid_Volume4,Ask_Price4,Ask_Volume4,"
                "Bid_Price5,Bid_Volume5,Ask_Price5,Ask_Volume5,"
                "Average_Price,Action_Day,Dayflag) (select * from tb_ctp_tickdata t where t.tradedate = :TRADEDATE and t.instrument_id = :ContractName )";
        query.prepare(querySql);
        query.bindValue(":ContractName"  ,ContractCode      );
        query.bindValue(":TRADEDATE"     ,date     );
        query.exec();

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            QSqlQuery update(QSqlDatabase::database(szDbName));
            dbChecked(update);
            QString UpdateSql = "update tb_ctp_tickdata_jy t set t.JINYUE_CODE = :JINYUE_CODE where t.tradedate = :CURR_DATE and t.instrument_id = :FUFU_CODE";
            update.prepare(UpdateSql);
            update.bindValue(":JINYUE_CODE"    ,jinyueCode);
            update.bindValue(":CURR_DATE"       ,date     );
            update.bindValue(":FUFU_CODE"       ,ContractCode);
            update.exec();

            QString szInsertErr = update.lastError().text();
            if(!szInsertErr.trimmed().isEmpty()) {
                qDebug() << szErr;
                query.clear();
                update.clear();
                return false;
            }else{
                qDebug() << "JinyueTickData Insert OK:"<<jinyueCode<< " "<<ContractCode;;
                query.clear();
                update.clear();
                return true;
            }
        }
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}

bool OracleDAO::SaveDayKLineData(QString ContractName,QString date,QString &szDbName){
    try {
        QSqlQuery query(QSqlDatabase::database(szDbName));
        dbChecked(query);

        //获取当日Tick数据中 最后一条带结算价的Tick数据 来计算当日的K线数据
        QString querySql("");
        querySql = "select * from (select * from TB_CTP_TICKDATA t "
                "where t.instrument_id = :ContractName and t.tradedate = :TRADEDATE "
                "and t.settlement_price > 0 and t.close_price > 0  and t.highest_price > 0 and t.lowest_price > 0 "
                "order by t.volume desc, t.update_time desc ,rownum desc ) where rownum <= 1";
        query.prepare(querySql);
        query.bindValue(":ContractName"  ,ContractName);
        query.bindValue(":TRADEDATE"     ,date     );
        query.exec();

        std::vector<QString> QueryValues;
        while (query.next()) { //Only One Row Data
            QueryValues.push_back(query.value("TRADEDATE").toString());//0
            QueryValues.push_back(query.value("UPDATE_TIME").toString());//1
            QueryValues.push_back(query.value("HIGHEST_PRICE").toString());//2
            QueryValues.push_back(query.value("LOWEST_PRICE").toString());//3
            QueryValues.push_back(query.value("OPEN_PRICE").toString());//4
            QueryValues.push_back(query.value("CLOSE_PRICE").toString());//5
            QueryValues.push_back(query.value("SETTLEMENT_PRICE").toString());//6
            QueryValues.push_back(query.value("PRE_SETTLEMENT_PRICE").toString());//7
            QueryValues.push_back(query.value("PRE_CLOSE_PRICE").toString());//8
            QueryValues.push_back(query.value("VOLUME").toString());//9 成交量
            //add start 2016.8.1
            QueryValues.push_back(query.value("PRE_OPEN_INTEREST").toString());//10 昨日持仓量
            QueryValues.push_back(query.value("OPEN_INTEREST").toString());//11 持仓量
            //add end   2016.8.1
        }

        QString szErr = query.lastError().text();
        if(!szErr.trimmed().isEmpty()) {
            qDebug() << szErr;
            query.clear();
            return false;
        }else{
            if(QueryValues.size() != 12){
                qDebug() << "No Relevant TickData:"<<ContractName;
            }else{
                qDebug() << "Get The Last TickData OK:"<<ContractName;

                QSqlQuery Insert(QSqlDatabase::database(szDbName));
                dbChecked(Insert);
                QString InsertSql = "insert into TB_KLINE_1DAY_CTP(FUTU_CODE,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,"
                        "OPEN_PRICE,CLOSE_PRICE,SETTLE_PRICE,PRE_SETTLEMENT,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT,"
						"BUSINESS_CHANGE,POSITION_AMMOUNT,POSITION_CHANGE) "
                        "values(:FUTU_CODE,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,"
                        ":CLOSE_PRICE,:SETTLE_PRICE,:PRE_SETTLEMENT,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT,"
                ///add start 2016.8.1
                        " :BUSINESS_CHANGE,:POSITION_AMMOUNT,:POSITION_CHANGE)";
                //计算成交量 和 成交变化量
                int BusinessAmount    = QueryValues[9].toInt();
                int PreBusinessAmount = 0;
                int BusinessChange    = 0;
                if(m_MapPreVolume.contains(ContractName)){
                    PreBusinessAmount = m_MapPreVolume[ContractName];
                }
                BusinessChange = BusinessAmount - PreBusinessAmount;
                //计算持仓量 和 持仓变化量
                int PreOpenInstrest = QueryValues[10].toInt();
                int OpenInstrest    = QueryValues[11].toInt();
                int PositionChange  = OpenInstrest - PreOpenInstrest;

                ///add end   2016.8.1
                Insert.prepare(InsertSql);
                Insert.bindValue(":FUTU_CODE"       ,ContractName      );
                Insert.bindValue(":CURR_DATE"       ,QueryValues[0]     );
                Insert.bindValue(":CURR_TIME"       ,QueryValues[1]     );
                Insert.bindValue(":MAX_PRICE"       ,QueryValues[2]     );
                Insert.bindValue(":MIN_PRICE"       ,QueryValues[3]     );
                Insert.bindValue(":OPEN_PRICE"      ,QueryValues[4]     );
                Insert.bindValue(":CLOSE_PRICE"     ,QueryValues[5]     );
                Insert.bindValue(":SETTLE_PRICE"    ,QueryValues[6]     );
                Insert.bindValue(":PRE_SETTLEMENT"  ,QueryValues[7]     );
                Insert.bindValue(":PRE_CLOSE_PRICE" ,QueryValues[8]     );
                Insert.bindValue(":BUSINESS_AMMOUNT" ,QueryValues[9]     ); //10 成交量
                ///add start 2016.8.1
                Insert.bindValue(":BUSINESS_CHANGE" ,BusinessChange     );  //11 成交 变化量
                Insert.bindValue(":POSITION_AMMOUNT" ,QueryValues[11]     );//12 持仓量
                Insert.bindValue(":POSITION_CHANGE" ,PositionChange     );  //13 持仓 变化量
                ///add end 2016.8.1
                Insert.exec();

                QString szInsertErr = Insert.lastError().text();
                if(!szInsertErr.trimmed().isEmpty()) {
                    qDebug() << szErr;
                    query.clear();
                    Insert.clear();
                    return false;
                }else{
                    qDebug() << "Insert OK:"<<ContractName;
                    query.clear();
                    Insert.clear();
                    return true;
                }
            }
        }
    } catch(std::exception &e) {
        qDebug() << e.what();
    }
}
//插入主连的5分钟K线
void OracleDAO::InsertZhulian5MinRec(QString InstrumentID, KLineType* Kline , QString date,QString time,QMap<QString,ContractType> &MapContractType){

    long CurBusinessAmount  = Kline->EndVolume - Kline->StartVolume;
    long BusinessChange     = CurBusinessAmount - Kline->PreBusinessAmount;
    long OpenInterest       = Kline->OpenInterest;
    long OpenInterestChange = OpenInterest - Kline->PreOpenInterest;
    QString tablename       = "";

    if(MapContractType.contains(InstrumentID)){
        ContractType oContract = MapContractType[InstrumentID];
        tablename = "TB_KLINE_ZL_5MIN_CTP_"+oContract.contractType;
    }else{
        tablename = "TB_KLINE_5MIN_CTP";
    }

    QSqlQuery Insert(m_OracleDB);
    dbChecked(Insert);
    QString InsertSql = "insert into " + tablename +"(FUTU_CODE,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,OPEN_PRICE,"
            "CLOSE_PRICE,SETTLE_PRICE,PRE_SETTLEMENT,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT,"
            "BUSINESS_CHANGE,POSITION_AMMOUNT,POSITION_CHANGE) "
            "values(:FUTU_CODE,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,"
            ":CLOSE_PRICE,:SETTLE_PRICE,:PRE_SETTLEMENT,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT,"
            ":BUSINESS_CHANGE,:POSITION_AMMOUNT,:POSITION_CHANGE )";
    Insert.prepare(InsertSql);
    Insert.bindValue(":FUTU_CODE",InstrumentID      );
    Insert.bindValue(":CURR_DATE",date     );
    Insert.bindValue(":CURR_TIME",time    );
    Insert.bindValue(":MAX_PRICE",Kline->MaxPrice);
    Insert.bindValue(":MIN_PRICE",Kline->MinPrice);
    Insert.bindValue(":OPEN_PRICE",Kline->OpenPrice);
    Insert.bindValue(":CLOSE_PRICE",Kline->ClosePrice);
    Insert.bindValue(":PRE_CLOSE_PRICE",Kline->PreClosePrice);
    Insert.bindValue(":BUSINESS_AMMOUNT",CurBusinessAmount);
    ///add start 2016.8.2
    Insert.bindValue(":BUSINESS_CHANGE", BusinessChange);
    Insert.bindValue(":POSITION_AMMOUNT", OpenInterest);
    Insert.bindValue(":POSITION_CHANGE", OpenInterestChange);
    ///add end   2016.8.2
    Insert.exec();

    QString szInsertErr = Insert.lastError().text();
    if(!szInsertErr.trimmed().isEmpty()) {
        qDebug() << szInsertErr;
        Insert.clear();
    }else{
        qDebug() << "5MinKLineTable Insert OK:"<<InstrumentID<< " "<<time;
        Insert.clear();
    }
}
//插入主连的1分钟K线
void OracleDAO::InsertZhulian1MinRec(QString InstrumentID, KLineType* Kline , QString date,QString time,QMap<QString,ContractType> &MapContractType){
    QString tablename = "";
    if(MapContractType.contains(InstrumentID)){
        ContractType oContract = MapContractType[InstrumentID];
        tablename = "TB_KLINE_ZL_1MIN_CTP_"+oContract.contractType;
    }else{
        tablename = "TB_KLINE_1MIN_CTP";
    }
            QSqlQuery Insert(m_OracleDB);
            dbChecked(Insert);

            QString InsertSql = "insert into " + tablename +"(FUTU_CODE,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,OPEN_PRICE, "
                    "CLOSE_PRICE,SETTLE_PRICE,PRE_SETTLEMENT,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT) "
                    "values(:FUTU_CODE,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,:CLOSE_PRICE,:SETTLE_PRICE,:PRE_SETTLEMENT,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT)";
            Insert.prepare(InsertSql);
            Insert.bindValue(":FUTU_CODE",InstrumentID      );
            Insert.bindValue(":CURR_DATE",date     );
            Insert.bindValue(":CURR_TIME",time    );
            Insert.bindValue(":MAX_PRICE",Kline->MaxPrice);
            Insert.bindValue(":MIN_PRICE",Kline->MinPrice);
            Insert.bindValue(":OPEN_PRICE",Kline->OpenPrice);
            Insert.bindValue(":CLOSE_PRICE",Kline->ClosePrice);
            Insert.bindValue(":PRE_CLOSE_PRICE",Kline->PreClosePrice);
            //使用K线结束时的成交量 减去 K线开始时的成交量 作为当前K线的成交量
            Insert.bindValue(":BUSINESS_AMMOUNT",Kline->EndVolume - Kline->StartVolume);
            Insert.exec();

            QString szInsertErr = Insert.lastError().text();
            if(!szInsertErr.trimmed().isEmpty()) {
                qDebug() << szInsertErr;
                //Match.clear();
                Insert.clear();
            }else{
                qDebug() << "1MinKLineTable Insert OK:"<<InstrumentID<< " "<<time;
                //Match.clear();
                Insert.clear();
            }
}
//插入5分钟K线记录
void OracleDAO::Insert5MinRec(QString InstrumentID, KLineType* Kline , QString date,QString time){

    if(Kline->PreBusinessAmount == 0){
        if(m_Pre5MinKLine.contains(InstrumentID)){
            Kline->PreBusinessAmount = m_Pre5MinKLine[InstrumentID].PreBusinessAmount;
        }
    }
    if(Kline->PreOpenInterest == 0){
        if(m_Pre5MinKLine.contains(InstrumentID)){
            Kline->PreOpenInterest = m_Pre5MinKLine[InstrumentID].PreOpenInterest;
        }
    }

    long CurBusinessAmount  = Kline->EndVolume - Kline->StartVolume;
    long BusinessChange     = CurBusinessAmount - Kline->PreBusinessAmount;
    long OpenInterest       = Kline->OpenInterest;
    long OpenInterestChange = OpenInterest - Kline->PreOpenInterest;

    QSqlQuery query(m_OracleDB);
    query.prepare("insert into TB_KLINE_5MIN_CTP(futu_code,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,OPEN_PRICE,CLOSE_PRICE,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT,"
                  "BUSINESS_CHANGE,POSITION_AMMOUNT,POSITION_CHANGE) "
                  "values(:futu_code,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,:CLOSE_PRICE,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT,"
                  ":BUSINESS_CHANGE,:POSITION_AMMOUNT,:POSITION_CHANGE)");
    query.bindValue(":futu_code", InstrumentID);
    query.bindValue(":CURR_DATE", date);
    query.bindValue(":CURR_TIME", time);
    query.bindValue(":MAX_PRICE", Kline->MaxPrice);
    query.bindValue(":MIN_PRICE", Kline->MinPrice);
    query.bindValue(":OPEN_PRICE", Kline->OpenPrice);
    query.bindValue(":CLOSE_PRICE", Kline->ClosePrice);
    query.bindValue(":PRE_CLOSE_PRICE", Kline->PreClosePrice);
    query.bindValue(":BUSINESS_AMMOUNT", CurBusinessAmount);
    ///add start 2016.8.2
    query.bindValue(":BUSINESS_CHANGE", BusinessChange);
    query.bindValue(":POSITION_AMMOUNT", OpenInterest);
    query.bindValue(":POSITION_CHANGE", OpenInterestChange);
    ///add end   2016.8.2
    query.exec();

    QString szErr = query.lastError().text();
    if(!szErr.trimmed().isEmpty()) {
        qDebug() << szErr;
    }else{
        qDebug() << "Insert5MinRec OK:"<<InstrumentID<<" "<< time;
    }
    query.clear();
}

//插入1分钟K线记录
void OracleDAO::Insert1MinRec(QString InstrumentID, KLineType* Kline , QString date,QString time){
    QSqlQuery query(m_OracleDB);
    query.prepare("insert into TB_KLINE_1MIN_CTP(futu_code,CURR_DATE,CURR_TIME,MAX_PRICE,MIN_PRICE,OPEN_PRICE,CLOSE_PRICE,PRE_CLOSE_PRICE,BUSINESS_AMMOUNT) "
                  "values(:futu_code,:CURR_DATE,:CURR_TIME,:MAX_PRICE,:MIN_PRICE,:OPEN_PRICE,:CLOSE_PRICE,:PRE_CLOSE_PRICE,:BUSINESS_AMMOUNT)");
    query.bindValue(":futu_code", InstrumentID);
    query.bindValue(":CURR_DATE", date);
    query.bindValue(":CURR_TIME", time);
    query.bindValue(":MAX_PRICE", Kline->MaxPrice);
    query.bindValue(":MIN_PRICE", Kline->MinPrice);
    query.bindValue(":OPEN_PRICE", Kline->OpenPrice);
    query.bindValue(":CLOSE_PRICE", Kline->ClosePrice);
    query.bindValue(":PRE_CLOSE_PRICE", Kline->PreClosePrice);
    //使用K线结束时的成交量 减去 K线开始时的成交量 作为当前K线的成交量
    query.bindValue(":BUSINESS_AMMOUNT", Kline->EndVolume - Kline->StartVolume);
    query.exec();

    QString szErr = query.lastError().text();
    if(!szErr.trimmed().isEmpty()) {
        qDebug() << szErr;
    }else{
        qDebug() << "Insert1MinRec OK:"<<InstrumentID<<" "<< time;
    }
    query.clear();
}

